﻿Imports System.Data
Imports System.Data.SqlClient
Public Class DALPaSala
    Dim cmd As SqlCommand
    Dim ret As Boolean
    Function inserir(ByVal hinicio As DateTime, ByVal hfinal As DateTime, ByVal coddia As Integer, ByVal codsala As Integer, ByVal descricao As String) As Boolean
        Try
            cmd = New SqlCommand
            cmd.CommandText = "INSERT INTO age_particularidades(sal_codigo, par_horainicio, par_horafinal, dia_codigo,par_detalhes)" & _
                 "VALUES(@codsala,@hinicio,@hfinal,@dia,@descricao)"

            cmd.Parameters.Add(New SqlParameter("@hinicio", SqlDbType.Time)).Value = hinicio.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@hfinal", SqlDbType.Time)).Value = hfinal.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@dia", SqlDbType.Int)).Value = coddia
            cmd.Parameters.Add(New SqlParameter("@codsala", SqlDbType.Int)).Value = codsala
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descricao
            ret = objconAgenda.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        If ret = True Then
            Return True
        Else
            Return False
        End If
    End Function
    Function Alterar(ByVal codigo As Integer, ByVal hinicio As DateTime, ByVal hfinal As DateTime, ByVal coddia As Integer, ByVal codsala As Integer, ByVal descricao As String) As Boolean
        Try
            cmd = New SqlCommand
            cmd.CommandText = "Update age_particularidades set sal_codigo=@codsala , par_horainicio =@hinicio , par_horafinal = @hfinal, dia_codigo=@dia ,par_detalhes= @descricao" & _
                 " where par_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            cmd.Parameters.Add(New SqlParameter("@hinicio", SqlDbType.Time)).Value = hinicio.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@hfinal", SqlDbType.Time)).Value = hfinal.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@dia", SqlDbType.Int)).Value = coddia
            cmd.Parameters.Add(New SqlParameter("@codsala", SqlDbType.Int)).Value = codsala
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descricao
            ret = objconAgenda.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        If ret = True Then
            Return True
        Else
            Return False
        End If
    End Function
    Function ValidaInsere(ByVal coddia As Integer, ByVal codsala As Integer, ByVal horainicio As DateTime, ByVal horafim As DateTime) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Dim ret As Boolean = True
        Try
            cmd = New SqlCommand
            cmd.CommandText = "SELECT * FROM vw_SalaParticularidade WHERE dia_codigo = @dia and sal_codigo = @salcod and " & _
                                "((par_horainicio BETWEEN @horainicio AND @horafim) OR (par_horafinal  BETWEEN @horainicio AND @horafim))"
            cmd.Parameters.Add(New SqlParameter("@dia", SqlDbType.Int)).Value = coddia
            cmd.Parameters.Add(New SqlParameter("@horainicio", SqlDbType.Time)).Value = horainicio.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@horafim", SqlDbType.Time)).Value = horafim.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@salcod", SqlDbType.Int)).Value = codsala
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function ValidaAlterar(ByVal codigo As Integer, ByVal coddia As Integer, ByVal codsala As Integer, ByVal horainicio As DateTime, ByVal horafim As DateTime) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Dim ret As Boolean = True
        Try
            cmd = New SqlCommand
            cmd.CommandText = "SELECT * FROM vw_SalaParticularidade WHERE dia_codigo = @dia and sal_codigo = @salcod and par_codigo<> @codigo and " & _
                                "((par_horainicio BETWEEN @horainicio AND @horafim) OR (par_horafinal  BETWEEN @horainicio AND @horafim))"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            cmd.Parameters.Add(New SqlParameter("@dia", SqlDbType.Int)).Value = coddia
            cmd.Parameters.Add(New SqlParameter("@horainicio", SqlDbType.Time)).Value = horainicio.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@horafim", SqlDbType.Time)).Value = horafim.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@salcod", SqlDbType.Int)).Value = codsala
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function localizarDia_Sala(ByVal dia As String, ByVal cod As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from  age_particulariedades where " & _
                " par_dia = @dia and sal_codigo = @cod "
            cmd.Parameters.Add(New SqlParameter("@dia", SqlDbType.VarChar)).Value = dia
            cmd.Parameters.Add(New SqlParameter("@cod", SqlDbType.VarChar)).Value = cod
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    'Function localizarDia(ByVal dia As String) As DataTable
    '    Dim cmd As SqlCommand
    '    Dim tab As DataTable
    '    Try
    '        cmd = New SqlCommand
    '        cmd.CommandText = "select * from  age_particulariedades where " & _
    '            " par_dia = @dia"
    '        cmd.Parameters.Add(New SqlParameter("@dia", SqlDbType.VarChar)).Value = dia
    '        tab = objconAgenda.executaConsulta(cmd)
    '    Catch ex As Exception
    '        Throw ex
    '    Finally
    '        cmd = Nothing
    '    End Try
    '    Return tab
    'End Function
    'Function localizartudo() As DataTable
    '    Dim cmd As SqlCommand
    '    Dim tab As DataTable
    '    Try
    '        cmd = New SqlCommand
    '        cmd.CommandText = "select * from  age_particulariedades"
    '        tab = objconAgenda.executaConsulta(cmd)
    '    Catch ex As Exception
    '        Throw ex
    '    Finally
    '        cmd = Nothing
    '    End Try
    '    Return tab
    'End Function
    Function localizartudo(ByVal Filtro As String) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from  vw_SalaParticularidade where  sal_descricao like @sala"
            cmd.Parameters.Add(New SqlParameter("@sala", SqlDbType.VarChar)).Value = "%" & Filtro & "%"
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function LocalizaParticularidade(ByVal codigo As Integer, ByVal filtro As String) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            Select Case codigo
                Case 0
                    cmd.CommandText = "select * from  vw_SalaParticularidade where  par_codigo = @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = filtro
                Case 1
                    cmd.CommandText = "select * from  vw_SalaParticularidade where  dia_descricao = @diasemana"
                    cmd.Parameters.Add(New SqlParameter("@diasemana", SqlDbType.VarChar)).Value = filtro
                Case 2
                    cmd.CommandText = "select * from  vw_SalaParticularidade where  sal_descricao like @sala"
                    cmd.Parameters.Add(New SqlParameter("@sala", SqlDbType.VarChar)).Value = "%" & filtro & "%"
            End Select


            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    'Function localizar() As DataTable
    '    Dim cmd As SqlCommand
    '    Dim tab As DataTable
    '    Try
    '        cmd = New SqlCommand
    '        cmd.CommandText = "select * from  gen_sala"
    '        tab = objconAgenda.executaConsulta(cmd)
    '    Catch ex As Exception
    '        Throw ex
    '    Finally
    '        cmd = Nothing
    '    End Try
    '    Return tab
    'End Function
    'Function localizar(ByVal cod As Integer) As DataTable
    '    Dim cmd As SqlCommand
    '    Dim tab As DataTable
    '    Try
    '        cmd = New SqlCommand
    '        cmd.CommandText = "select * from  gen_sala where " & _
    '            " sal_codigo = @cod"
    '        cmd.Parameters.Add(New SqlParameter("@cod", SqlDbType.Int)).Value = cod
    '        tab = objconAgenda.executaConsulta(cmd)
    '    Catch ex As Exception
    '        Throw ex
    '    Finally
    '        cmd = Nothing
    '    End Try
    '    Return tab
    'End Function

    'Function localizar(ByVal sala As String) As DataTable
    '    Dim cmd As SqlCommand
    '    Dim tab As DataTable
    '    Try
    '        cmd = New SqlCommand
    '        cmd.CommandText = "select par_codigo,sal_descricao,par_horainicio,par_horafinal,par_dia,par_motivo from  age_particulariedades where " & _
    '            " sal_descricao like @sala"
    '        cmd.Parameters.Add(New SqlParameter("@sala", SqlDbType.VarChar)).Value = "%" & sala & "%"
    '        tab = objconAgenda.executaConsulta(cmd)
    '    Catch ex As Exception
    '        Throw ex
    '    Finally
    '        cmd = Nothing
    '    End Try
    '    Return tab
    'End Function
    Function excluir(ByVal codigo As Integer)
        Try
            cmd = New SqlCommand
            cmd.CommandText = "delete from age_particularidades" & _
                " where par_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            ret = objconAgenda.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function

End Class
